package dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import model.Student;
import model.User;
import model.WeiGui;
import utils.DBconn;

public class WeiGuiDao {

	//数据库连接
	// 删除原有的getConnection方法
//增加违规表信息
	public boolean addMsg(WeiGui wg) {
//		测试行
	       System.out.println("addMsgid:"+wg.getId()+"  time:"+wg.getTime()+"  type:"+wg.getType()+" because:"+wg.getBecause()+"roomid"+wg.getAddress());

		// TODO Auto-generated method stub
		  String sql = "INSERT INTO weigui (id, time, type, because, address) VALUES (?, ?, ?, ?, ?)";
	        try (Connection conn = DBconn.getConnection();
	             PreparedStatement stmt = conn.prepareStatement(sql)) {
	            stmt.setString(1, wg.getId());
	            stmt.setString(2, wg.getTime());
	            stmt.setString(3, wg.getType());
	            stmt.setString(4, wg.getBecause());
	            stmt.setString(5, wg.getAddress());
	            return stmt.executeUpdate() > 0;
	        } catch (SQLException e) {
	            e.printStackTrace();
	        }
	        return false;
	}

	
	
//	更新违规表信息
    public boolean updateMsg(WeiGui wg,String oldid,String oldtime,String oldtype) {
        String sql = "UPDATE weigui SET  time = ? ,type = ? ,because = ?,address = ?,id = ? WHERE id = ? AND time = ? AND type = ? ";
        try (Connection conn = DBconn.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            
        	 stmt.setString(1, wg.getTime());
             stmt.setString(2, wg.getType());
             stmt.setString(3, wg.getBecause());
             stmt.setString(4, wg.getAddress());
             stmt.setString(5, wg.getId());
             stmt.setString(6, oldid);
             stmt.setString(7, oldtime);
             stmt.setString(8, oldtype);
             
            return stmt.executeUpdate() > 0;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    
//删除违规表
    public boolean deleteMsg(String id, String time, String type) {
		// TODO Auto-generated method stub
    	 String sql = "DELETE FROM weigui WHERE id = ? AND time = ? AND type = ? ";
         try (Connection conn = DBconn.getConnection();
              PreparedStatement stmt = conn.prepareStatement(sql)) {
             stmt.setString(1, id);
             stmt.setString(2, time);
             stmt.setString(3, type);
             return stmt.executeUpdate() > 0;
         } catch (SQLException e) {
             e.printStackTrace();
         }
         return false;
    }
//    查找用户是否存在
    public boolean checkStudentIDExists(String id) {
		// TODO Auto-generated method stub
    	boolean exists = false;
        try (Connection conn = DBconn.getConnection()) {
            String sql = "SELECT COUNT(*) FROM student WHERE id = ?";
            try (PreparedStatement stmt = conn.prepareStatement(sql)) {
                stmt.setString(1, id);
                ResultSet rs = stmt.executeQuery();
                if (rs.next() && rs.getInt(1) > 0) {
                    exists = true;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return exists;
	}
//**********************************学生功能**************************************************************
//	列表显示该学生所有的违规信息情况
    public List<WeiGui> getAllmsg() {
        List<WeiGui> wgs = new ArrayList<>();
        String sql = "SELECT * FROM weigui";
        try (Connection conn = DBconn.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
            	WeiGui wg = new WeiGui();
            	wg.setId(rs.getString("id"));
            	wg.setTime(rs.getString("time"));
            	wg.setType(rs.getString("type"));
            	wg.setBecause(rs.getString("because"));
            	wgs.add(wg);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return wgs;
    }
 // 获取总行数——针对学生
    public int getStudentCount(String id) {
    	
        int count = 0;
        String sql = "SELECT COUNT(*) FROM weigui WHERE id = ?";  // 假设表名为 users
        try (Connection conn = DBconn.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {
           		stmt.setString(1, id);
           		ResultSet rs = stmt.executeQuery();
               if (rs.next()) {
            	   count = rs.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }
// 获取指定页数据——针对学生
    public List<WeiGui> getWGStudentPage(String id, int currentPage, int pageSize) {
        List<WeiGui> wgs = new ArrayList<>();
        String sql = "SELECT * FROM weigui WHERE id = ? LIMIT ?, ?";
        try (Connection conn = DBconn.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
        	stmt.setString(1, id);
            stmt.setInt(2, (currentPage - 1) * pageSize);  // 计算偏移量
            stmt.setInt(3, pageSize);  // 设置每页显示的记录数
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
            	WeiGui wg = new WeiGui();
            	wg.setId(rs.getString("id"));
            	wg.setTime(rs.getString("time"));
            	wg.setType(rs.getString("type"));
            	wg.setBecause(rs.getString("because"));
            	wgs.add(wg);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return wgs;
    }
//***************************************宿管功能*************************************************
 // 获取总行数——针对宿管
    public int getSGCount(String build) {
    	 int count = 0;
    	 String buildvalue=build+"%";
    	 String sql = "SELECT COUNT(*) FROM weigui WHERE address LIKE ?";  // 假设表名为 users
        try (Connection conn = DBconn.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {
           		stmt.setString(1, buildvalue);
           		ResultSet rs = stmt.executeQuery();
               if (rs.next()) {
            	   count = rs.getInt(1);
            	   //测试行
            	   System.out.println("宿管看到的的行数是: " + count);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }
// 获取指定页数据——针对宿管
    public List<WeiGui> getWGSGPage(String build, int currentPage, int pageSize) {
        List<WeiGui> wgs = new ArrayList<>();
        String buildvalue=build+"%";
        String sql = "SELECT * FROM weigui WHERE address LIKE ? LIMIT ?, ?";
        try (Connection conn = DBconn.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
        	stmt.setString(1, buildvalue);
            stmt.setInt(2, (currentPage - 1) * pageSize);  // 计算偏移量
            stmt.setInt(3, pageSize);  // 设置每页显示的记录数
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
            	WeiGui wg = new WeiGui();
            	wg.setId(rs.getString("id"));
            	wg.setTime(rs.getString("time"));
            	wg.setType(rs.getString("type"));
            	wg.setBecause(rs.getString("because"));
            	wg.setAddress(rs.getString("address"));
            	wgs.add(wg);
            }
            //测试行
     	   System.out.println("到达getWGSGPage，build："+build);
   
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return wgs;
    }
//通过id、时间和类型获取单行的数据
    public WeiGui getmsgBythree(String id, String time, String type) {
		// TODO Auto-generated method stub
    	
    	String sql = "SELECT * FROM weigui WHERE id = ? AND time = ? AND type = ?";
        try (Connection conn = DBconn.getConnection();
        		 PreparedStatement stmt = conn.prepareStatement(sql)) {
        	stmt.setString(1, id);
            stmt.setString(2, time); 
            stmt.setString(3, type);  
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
            	
            	WeiGui wg = new WeiGui();
            	wg.setId(rs.getString("id"));
            	wg.setTime(rs.getString("time"));
            	wg.setType(rs.getString("type"));
            	wg.setBecause(rs.getString("because"));
            	wg.setAddress(rs.getString("address"));
            	return wg;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
	     
	}
//    获取指定楼栋的宿舍号
    public List<Student> getAllroom(String build) {
        List<Student> wgs = new  ArrayList<>();
        String sql = "SELECT DISTINCT roomid FROM student WHERE roomid LIKE ?";
        String buildvalue=build+"%";
        try (Connection conn = DBconn.getConnection();
        	PreparedStatement stmt = conn.prepareStatement(sql)) {
        	stmt.setString(1, buildvalue);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
            	Student wg = new Student();
            	wg.setRoomid(rs.getString("roomid"));
            	//测试行
            	System.out.println("getAllroom:"+rs.getString("roomid"));
            	wgs.add(wg);
//            	测试行
            	for (Student wgss :wgs) {
            		//测试行
                	System.out.println("getAllroom存入List之后:"+wgss);
            	}
            }
        }catch (SQLException e) {
                e.printStackTrace();
            }
        return wgs;
    }
//查找所有学生学号
	public List<User> getAllid() {
		// TODO Auto-generated method stub
		List<User> wgs = new ArrayList<>();	
		String sql = "SELECT id FROM student";
        try (Connection conn = DBconn.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            while (rs.next()) {
            	User wg = new User();
            	wg.setId(rs.getString("id"));
            	wgs.add(wg);
            }
        }catch (SQLException e) {
            e.printStackTrace();
        } 
        return wgs;  
	}
//******************************************辅导员功能类**************************************
// 获取总行数——针对老师
    public int getFDYCount(List<String> idclass) {
    	
        int count = 0;
        String sql = "SELECT COUNT(*) " +buildSqlQuery(idclass);
        //String sql = "SELECT COUNT(*) FROM weigui WHERE id = ?";  // 假设表名为 users
        //测试行
        System.out.println("getFDYCount:"+sql);
        try (Connection conn = DBconn.getConnection();
                PreparedStatement stmt = conn.prepareStatement(sql)) {
        		//循环输入class值
        		for(int i=1;i<idclass.size();i++) {
        			stmt.setString(i, idclass.get(i));
        		//测试行，康康循环了几次
        		System.out.println("getFDYCount循环了："+i);
        		}
        		
           		//查询总行数
        		ResultSet rs = stmt.executeQuery();
        		if (rs.next()) {
            	   count = rs.getInt(1);
        		}
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }
 // 获取指定页数据——针对老师
    public List<WeiGui> getWGFDYPage(List<String> idclass, int currentPage, int pageSize) {
    	//for循环是在测试这个idclass的会话到底有无存入班级
    	for (int i = 0; i < idclass.size(); i++) {
    	    System.out.println("getWGFDYPage:"+idclass.get(i));
    	}
    	List<WeiGui> wgs = new ArrayList<>();
        String sql = "SELECT weigui.* " +buildSqlQuery(idclass)+"LIMIT ?, ?;";
        System.out.println("getWGFDYPage:"+sql);
        try (Connection conn = DBconn.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
        	//循环输入class值
        	int i;
        	for(i=1;i<idclass.size();i++) {
        		stmt.setString(i, idclass.get(i));
        	}
        	
            stmt.setInt(i, (currentPage - 1) * pageSize);  // 计算偏移量
            stmt.setInt(i+1, pageSize);  // 设置每页显示的记录数
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
            	WeiGui wg = new WeiGui();
            	wg.setId(rs.getString("id"));
            	wg.setTime(rs.getString("time"));
            	wg.setType(rs.getString("type"));
            	wg.setBecause(rs.getString("because"));
            	wgs.add(wg);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return wgs;
    }
 //需要针对老师的班级动态去建一个查询语句
    public String buildSqlQuery(List<String> classArray) {
        // 构建 IN 子句的部分
        StringBuilder inClass = new StringBuilder();
        for (int i =1; i < classArray.size(); i++) {
        	inClass.append("?");
            if (i < classArray.size() - 1) {
            	inClass.append(", ");
            }
        }

        // 完整的 SQL 查询语句
        String sql = 
                     "FROM student " +
                     "JOIN weigui ON student.id = weigui.id " +
                     "WHERE student.class IN (" + inClass.toString() + ") ";
        //测试行：查看输出的sql语句
        System.out.println("buildSqlQuery:"+sql);

        return sql;
    }



	
}
